CREATE PROCEDURE [dbo].[sp_Get_ZipHistory]
(
	@AccountID varchar(10),
	@Month INT,
	@Year INT,
	@Type INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	IF (@Type = 0)
		BEGIN
			select zh.ZipID, substring(zh.Zip,1,5) as ZipName, zl.County, zh.MdNumber, 
			zl.State, zl.City, zl.Division, zl.Market, zl.Territory,
			zl.ActiveLeadCount, zl.LapsedLeadCount, zl.BusinesLeadCount, zl.NeighborLeadCount,
			zh.OneALN, zh.OneBus, zh.TwoALN, zh.TwoBus, zh.ThreeALN, zh.ThreeBus, zh.FourALN, zh.FourBus, zh.FiveALN, zh.FiveBus, zh.SixALN, zh.SixBus, 
			zh.Zip, zl.Latitude, zl.Longitude, zl.AgentName, zl.ActiveStopCount, zl.LapsedStopCount, zl.PreferCity, zh.OrderDate
			from (
				select z.ZipID, zi.MdNumber, z.Zip,
				sum(case when o.PeroidMonth = @Month and o.PeroidYear = @Year and (oz.Type = 0 or oz.Type = 1) then 1 else 0 end) as OneALN,
				sum(case when o.PeroidMonth = @Month and o.PeroidYear = @Year and (oz.Type = 0 or oz.Type = 2) then 1 else 0 end) as OneBus,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as TwoALN,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as TwoBus,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as ThreeALN,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as ThreeBus,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as FourALN,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as FourBus,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as FiveALN,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as FiveBus,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as SixALN,
				sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 0) and 
				o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as SixBus,
				max(zo.OrderDate) as OrderDate
				from dbo.Zips z
				inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
				left join dbo.OrderZip oz on z.ZipID = oz.ZipID
				left join dbo.Orders o on o.OrderID = oz.OrderID
				left join
				(
					select z.ZipID as ZipID, max(dbo.sf_GetOrderDate(o.PeroidYear, o.PeroidMonth)) as OrderDate from dbo.Zips z
					inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
					inner join dbo.OrderZip oz on z.ZipID = oz.ZipID 
					inner join dbo.Orders o on o.OrderID = oz.OrderID and o.IsPurchased = 1
					where zi.MdNumber = @AccountID and (o.PeroidMonth <> @Month or o.PeroidYear <> @Year)
					group by z.ZipID
				)zo on z.ZipID = zo.ZipID
				where zi.MdNumber = @AccountID 
				group by z.ZipID, z.Zip, zi.MdNumber
			)zh inner join ZipInfo zl
			on zh.ZipID = zl.ZipID
			where zl.MdNumber = @AccountID order by zl.Territory, zl.County, zl.City, zh.Zip
		END
	ELSE
		BEGIN
			select zh.ZipID, substring(zh.Zip,1,5) as ZipName, zl.County, zh.MdNumber, 
			zl.State, zl.City, zl.Division, zl.Market, zl.Territory,
			case when zh.Type = 2 then 0 else zl.ActiveLeadCount end,
			case when zh.Type = 2 then 0 else zl.LapsedLeadCount end,
			case when zh.Type = 1 then 0 else zl.BusinesLeadCount end,
			case when zh.Type = 2 then 0 else zl.NeighborLeadCount end, zh.Type, zh.Zip, zl.Latitude, zl.Longitude, zl.AgentName, zl.PreferCity
			from (
				select z.ZipID, zi.MdNumber, z.Zip, oz.Type
				from dbo.Zips z
				inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
				left join dbo.OrderZip oz on z.ZipID = oz.ZipID
				left join dbo.Orders o on o.OrderID = oz.OrderID
				where zi.MdNumber = @AccountID
				group by z.ZipID, z.Zip, zi.MdNumber, oz.Type
				having sum(case when o.PeroidYear = @Year and o.PeroidMonth = @Month then 1 else 0 end) > 0
			)zh inner join ZipInfo zl
			on zh.ZipID = zl.ZipID
			where zl.MdNumber = @AccountID order by zl.Territory, zl.County, zl.City, zh.Zip
		END
END
